﻿//-------------------------------------------------------------------
//文件名称：TPaper.cs
//模块名称：TPaper数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;
using ELearning.Models.ViewModel.YTJYAdmin.Paper;
using ELearning.Models.ViewModel.YTJYAdmin.Study;

namespace ELearning.DAL
{
    /// <summary>
    /// TPaper数据访问层
    /// </summary>
    public partial class TPaperRepository : BaseRepository<TPaper>
    {
        /// <summary>
        /// 获取试卷列表
        /// </summary>
        public List<TPaperModel> GetModelList(TPaperRequest request)
        {
            var args = new List<SqlParameter>();
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.Name))
                where = $" AND v.Name LIKE N'%{request.Name}%'";

            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }

            var sql = $@"

                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    v.*,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() Total
		                    FROM
			                    dbo.TPaper v
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  AND v.IsUsed = 1 {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<TPaperModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取试卷相关或者不相关的列表
        /// </summary>
        public List<TPaperQuestionModel> GetModelRelevantList(TPaperQuestionRequest request)
        {
            var args = new List<SqlParameter>();
            var where = "AND cv.PaperId IS NOT NULL";
            if (request.Type == 0)
                where = $" AND cv.PaperId IS NULL";
            if (!string.IsNullOrEmpty(request.Name))
                where = $" {where}  AND v.Name LIKE N'%{request.Name}%'";
            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }

            var sql = $@"
                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    cv.PaperId,
								v.Id,
								v.Name,
								v.MainName,
								v.Difficulty,
								v.Analysis,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
								qt.TypeName QuestionTypeName,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount
		                    FROM
			                    dbo.TQuestion v
								LEFT JOIN dbo.TPaperQuestion cv ON cv.QuestionId = v.Id AND cv.IsDelete = 0  AND cv.PaperId = '{request.PaperId}'
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
								LEFT JOIN dbo.TQuestionType qt ON qt.TypeCode = v.QuestionTypeCode AND qt.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<TPaperQuestionModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取客户端的试卷列表
        /// </summary>
        public List<SitePaperModel> GetSitePaperList(SitePaperRequest request, Guid userId)
        {
            var sql = $@"
                SELECT
	                *
                FROM
	                (
		                SELECT
			                p.id,
			                p.Name,
			                p.Description,p.Type,
			                (SELECT COUNT(1) FROM dbo.TPaperTestHistory ph WHERE ph.IsDelete = 0 AND ph.UserId = @userId AND ph.PaperId = p.id)DoCount,
			                ROW_NUMBER() OVER(ORDER BY p.UpdateTime DESC)rn,
			                COUNT(1) OVER() TotalCount
		                FROM
			                dbo.TPaper p
		                WHERE
			                p.IsDelete = 0
			                AND p.IsUsed = 1
			                AND p.Type = @Type
	                )a
                WHERE
	                a.rn BETWEEN @StartIndex AND @EndIndex";
            var args = new List<SqlParameter>
            {
                new SqlParameter("@StartIndex",request.StartIndex),
                new SqlParameter("@EndIndex",request.EndIndex),
                new SqlParameter("@Type",request.Type),
                new SqlParameter("@userId",userId)
            }.ToArray();

            return nContext.Database.SqlQuery<SitePaperModel>(sql, args).ToList();
        }

        /// <summary>
        /// 获取试卷详情
        /// </summary>
        public List<SitePaperDetailModel> GetSitePaperDetail(Guid paperId)
        {
            var sql = $@"
                    SELECT
	                    pq.QuestionId,qi.ID QuestionItemId,
	                    q.MainName,
	                    qi.Name QuestionName,
	                    q.QuestionTypeCode,
	                    qt.TypeName QuestionTypeName,
	                    p.Type,
	                    p.TotalScore,
	                    p.TimeSpan,
	                    qi.Grade,
	                    qi.Analysis,qi.Answer,
	                    o.Id OptionId,
	                    o.Name OptionName,
						qi.Difficulty,
						qi.Sort,
						qi.RightAnswer,rqo.Sort QoSort,
	                    rqo.IsRight,p.Name,p.Description,p.TotalCount,p.TypeCode,p.CategoryId,p.Year,p.OrgCode
                    FROM
	                    dbo.TPaper p
	                    LEFT JOIN dbo.TPaperQuestion pq ON p.Id = pq.PaperId
	                    LEFT JOIN dbo.TQuestion q ON pq.QuestionId = q.Id
	                    LEFT JOIN dbo.TQuestionItem qi ON qi.QuestionId = q.Id
						LEFT JOIN dbo.RQuestionOption rqo ON rqo.QuestionItemId = qi.Id AND rqo.IsDelete = 0
						LEFT JOIN dbo.TOption o ON o.Id = rqo.OptionId
	                    LEFT JOIN dbo.TQuestionType qt ON qt.TypeCode = q.QuestionTypeCode
                    WHERE
	                    pq.IsDelete = 0 AND q.IsDelete = 0 
	                    AND pq.PaperId = @PaperId";
            var args = new List<SqlParameter>
            {
                new SqlParameter("@PaperId",paperId)
            }.ToArray();
            return nContext.Database.SqlQuery<SitePaperDetailModel>(sql, args).ToList();
        }

        /// <summary>
        /// 获取答题记录信息
        /// </summary>
        /// <param name="paperHistoryId">历史记录id</param>
        public List<DonePaperDetailModel> GetDonePaperDetail(Guid paperHistoryId)
        {
            var sql = $@"
                    SELECT
	                    p.*,
	                    q.QuestionId,q.QuestionItemId,q.InputAnswer,
	                    q.MainName,
	                    q.Name QuestionName,
	                    qt.TypeCode QuestionTypeCode,
	                    qt.TypeName QuestionTypeName,
	                    q.Analysis,q.Answer,q.InputAnswer,
	                    q.RightAnswer,
	                    q.ChoosedOptionId,
	                    q.UserGrade,
	                    q.Grade,
	                    q.Status QStatus,
	                    p.OrgCode,
	                    o.OptionId,
	                    o.Name OptionName,
	                    o.IsRight,
	                    o.IsChoosed
                    FROM
	                    dbo.TPaperTestHistory p
	                    LEFT JOIN dbo.TPaperQuestionTestHistory q ON q.PaperTestHistoryId = p.Id
	                    LEFT JOIN dbo.TPaperOptionTestHistory o ON o.PaperQuestionTestHistoryId = q.Id AND o.IsDelete = 0 
	                    LEFT JOIN dbo.TQuestionType qt ON qt.TypeCode = q.QuestionTypeCode
                    WHERE
	                    p.IsDelete = 0 AND q.IsDelete = 0 	                   
	                    AND p.Id = @paperHistoryId";
            var args = new List<SqlParameter>
            {
                new SqlParameter("@paperHistoryId",paperHistoryId)
            }.ToArray();

            return nContext.Database.SqlQuery<DonePaperDetailModel>(sql, args).ToList();
        }
    }
}


